﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WaveMaster.Holidays.Club.Gateway;
using WaveMaster.Holidays.Club.Domain;
using System.Collections;
using MySql.Data.MySqlClient;
using WaveMaster.Holidays.Club.SQL;
using System.Data;

namespace WaveMaster.Holidays.Club.Service
{
    public class TravelAgentService : IService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        public TravelAgentService(DataBaseConnectivity dataBaseConectivity)
        {
            m_dataBaseConectivity = dataBaseConectivity;
        }
        public TravelAgentService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity(); ;
        }

        public IList LoadAllTravelAgent()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TravelAgentSQL.GetAllTravelAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    TravelAgent user = new TravelAgent(rd);
                    result.Add(user);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public TravelAgent GetTravelAgentByID(long id)
        {
            TravelAgent result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TravelAgentSQL.GetTravelAgentByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = id;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new TravelAgent(rd);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public IList SearchTravelAgentByCodeName(string p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TravelAgentSQL.SearchTravelAgentByCodeName(p), m_dataBaseConectivity.InterfaceSqlConnection);
                //cmd.Parameters.Add("code", MySqlDbType.VarChar).Value = p;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new TravelAgent(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public IList SearchActiveTravelAgentByCodeName(string p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TravelAgentSQL.SearchActiveTravelAgentByCodeName(p), m_dataBaseConectivity.InterfaceSqlConnection);
                //cmd.Parameters.Add("code", MySqlDbType.VarChar).Value = p;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new TravelAgent(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public void DeleteTravelAgent(TravelAgent user)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TravelAgentSQL.DeleteTravelAgentByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("travelAgentId", MySqlDbType.Int32).Value = user.TravelAgentID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void UpdateTravelAgent(TravelAgent m_user)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TravelAgentSQL.UpdateTravelAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_code", MySqlDbType.VarChar).Value = m_user.Code;
                cmd.Parameters.Add("m_name", MySqlDbType.VarChar).Value = m_user.Name;
                cmd.Parameters.Add("m_address", MySqlDbType.VarChar).Value = m_user.Address;
                cmd.Parameters.Add("m_zipcode", MySqlDbType.VarChar).Value = m_user.ZipCode;
                cmd.Parameters.Add("m_contactPerson", MySqlDbType.VarChar).Value = m_user.ContactPerson;
                cmd.Parameters.Add("m_phone", MySqlDbType.VarChar).Value = m_user.Phone;
                cmd.Parameters.Add("m_fax", MySqlDbType.VarChar).Value = m_user.Fax;
                cmd.Parameters.Add("m_email", MySqlDbType.VarChar).Value = m_user.Email;
                cmd.Parameters.Add("m_website", MySqlDbType.VarChar).Value = m_user.WebSite;
                cmd.Parameters.Add("m_active", MySqlDbType.VarChar).Value = m_user.Active;
                cmd.Parameters.Add("m_modfctnDate", MySqlDbType.Datetime).Value = DateTime.Now; //m_user.UserName;
                cmd.Parameters.Add("m_modfctnBy", MySqlDbType.Int32).Value = m_user.ModificationBy;
                cmd.Parameters.Add("m_memberType", MySqlDbType.VarChar).Value = m_user.MemberTypeEnum.ToString();
                cmd.Parameters.Add("m_registrationDate", MySqlDbType.Datetime).Value = m_user.RegisterDate;
                cmd.Parameters.Add("m_expiredDate", MySqlDbType.Datetime).Value = m_user.ExpiredDate;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = m_user.TravelAgentID;
                cmd.Parameters.Add("m_regNo", MySqlDbType.VarChar).Value = m_user.RegNo;
                cmd.Parameters.Add("m_firstName", MySqlDbType.VarChar).Value = m_user.FirstName;
                cmd.Parameters.Add("m_lastName", MySqlDbType.VarChar).Value = m_user.LastName;
                cmd.Parameters.Add("m_gender", MySqlDbType.VarChar).Value = m_user.Gender;
                cmd.Parameters.Add("m_mobilePhone", MySqlDbType.VarChar).Value = m_user.MobilePhone;
                cmd.Parameters.Add("m_memberEmail", MySqlDbType.VarChar).Value = m_user.MemberEmail;
                cmd.Parameters.Add("m_member_type_id", MySqlDbType.Int32).Value = m_user.MemberType.m_member_type_id;
                
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            } 
        }
        public void UpdateTicketDeductionAutoNUmber()
        {
            IList result = new ArrayList();
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
            cmd.CommandText = TravelAgentSQL.GetAllTravelAgent();
            MySqlDataReader rd = cmd.ExecuteReader();
            while (rd.Read())
            {
                TravelAgent user = new TravelAgent(rd);
                result.Add(user);
            }
            rd.Close();
            foreach (TravelAgent ta in result)
            {
                cmd.Parameters.Clear();
                cmd = new MySqlCommand(AutoNumberSQL.GetLatestNumberByAgentAndType(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = ta.TravelAgentID;
                cmd.Parameters.Add("type", MySqlDbType.VarChar).Value = "TicketDeduction";
                object res = cmd.ExecuteScalar();
                if (res == null)
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = AutoNumberSQL.InsertAutoNumber();
                    cmd.Parameters.Add("m_travelAgentId", MySqlDbType.VarChar).Value = ta.TravelAgentID;
                    cmd.Parameters.Add("type", MySqlDbType.VarChar).Value = "TicketDeduction";
                    cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = 0;
                    cmd.ExecuteNonQuery();
                }
            }
            m_dataBaseConectivity.InterfaceSqlConnection.Close();
        }

        public void InserTravelAgent(TravelAgent m_travelAgent)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                //GEt AUTO NUMBER
                bool auto = m_travelAgent.Code.ToLower() == "autonumber";
                MySqlCommand cmd = new MySqlCommand(); 
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                if (auto)
                {
                    cmd.CommandText = AutoNumberSQL.GetLatestNumberByAgentAndType();
                    cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = 1;//default
                    cmd.Parameters.Add("type", MySqlDbType.VarChar).Value = "TravelAgentCode";
                    int nextnumber = Convert.ToInt32(cmd.ExecuteScalar()) + 1;
                    string nextnumbers = nextnumber.ToString().PadLeft(4, '0');
                    m_travelAgent.Code = "WMM-" + nextnumbers;
                }
                //Insert TA
                cmd.Parameters.Clear();
                cmd.CommandText = TravelAgentSQL.InsertTravelAgent();
                cmd.Parameters.Add("m_code", MySqlDbType.VarChar).Value = m_travelAgent.Code;
                cmd.Parameters.Add("m_name", MySqlDbType.VarChar).Value = m_travelAgent.Name;
                cmd.Parameters.Add("m_address", MySqlDbType.VarChar).Value = m_travelAgent.Address;
                cmd.Parameters.Add("m_zipcode", MySqlDbType.VarChar).Value = m_travelAgent.ZipCode;
                cmd.Parameters.Add("m_contactPerson", MySqlDbType.VarChar).Value = m_travelAgent.ContactPerson;
                cmd.Parameters.Add("m_phone", MySqlDbType.VarChar).Value = m_travelAgent.Phone;
                cmd.Parameters.Add("m_fax", MySqlDbType.VarChar).Value = m_travelAgent.Fax;
                cmd.Parameters.Add("m_email", MySqlDbType.VarChar).Value = m_travelAgent.Email;
                cmd.Parameters.Add("m_website", MySqlDbType.VarChar).Value = m_travelAgent.WebSite;
                cmd.Parameters.Add("m_active", MySqlDbType.VarChar).Value = m_travelAgent.Active;
                cmd.Parameters.Add("m_cretnDate", MySqlDbType.Datetime).Value = DateTime.Now;//m_user.CreatenDate;
                cmd.Parameters.Add("m_cretnBy", MySqlDbType.Int32).Value = m_travelAgent.CreatenBy;
                cmd.Parameters.Add("m_modfctnDate", MySqlDbType.Datetime).Value = DateTime.Now; //m_user.UserName;
                cmd.Parameters.Add("m_modfctnBy", MySqlDbType.Int32).Value = m_travelAgent.CreatenBy;
                cmd.Parameters.Add("m_memberType", MySqlDbType.VarChar).Value = m_travelAgent.MemberTypeEnum.ToString();
                cmd.Parameters.Add("m_registrationDate", MySqlDbType.Datetime).Value = m_travelAgent.RegisterDate;
                cmd.Parameters.Add("m_expiredDate", MySqlDbType.Datetime).Value = m_travelAgent.ExpiredDate;
                cmd.Parameters.Add("m_regNo", MySqlDbType.VarChar).Value = m_travelAgent.RegNo;
                cmd.Parameters.Add("m_firstName", MySqlDbType.VarChar).Value = m_travelAgent.FirstName;
                cmd.Parameters.Add("m_lastName", MySqlDbType.VarChar).Value = m_travelAgent.LastName;
                cmd.Parameters.Add("m_gender", MySqlDbType.VarChar).Value = m_travelAgent.Gender;
                cmd.Parameters.Add("m_mobilePhone", MySqlDbType.VarChar).Value = m_travelAgent.MobilePhone;
                cmd.Parameters.Add("m_memberEmail", MySqlDbType.VarChar).Value = m_travelAgent.MemberEmail;
                cmd.Parameters.Add("m_member_type_id", MySqlDbType.Int32).Value = m_travelAgent.MemberType.m_member_type_id;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.CommandText = TravelAgentSQL.GetMaxTravelAgentID();
                m_travelAgent.TravelAgentID = Convert.ToInt64(cmd.ExecuteScalar());
                cmd.Parameters.Clear();
                //insert auto ticket purchase number
                cmd.CommandText = AutoNumberSQL.InsertAutoNumber();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.VarChar).Value = m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("type", MySqlDbType.VarChar).Value ="TicketPurchase";
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = 0;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                //insert auto number booking
                cmd.CommandText = AutoNumberSQL.InsertAutoNumber();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.VarChar).Value = m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("type", MySqlDbType.VarChar).Value = CheckInStatus.Booking;
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = 0;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                //insert auto number checkIn
                cmd.CommandText = AutoNumberSQL.InsertAutoNumber();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.VarChar).Value = m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("type", MySqlDbType.VarChar).Value = CheckInStatus.CheckIn;
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = 0;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                //insert auto number TicketDeduction
                cmd.CommandText = AutoNumberSQL.InsertAutoNumber();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.VarChar).Value = m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("type", MySqlDbType.VarChar).Value = "TicketDeduction";
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = 0;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                //insert book stock
                cmd.CommandText = BookingSQL.InsertBookStockByTravelAgent();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.VarChar).Value = m_travelAgent.TravelAgentID;
                cmd.Parameters.Add("type", MySqlDbType.VarChar).Value = "TicketPurchase";
                cmd.ExecuteNonQuery();
                //UPDATE NEXT NUMBER 
                if (auto)
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = AutoNumberSQL.UpdateLatestAutoNumber();
                    cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = 1;
                    cmd.Parameters.Add("type", MySqlDbType.VarChar).Value = "TravelAgentCode";
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public IList LoadAllActiveTravelAgent()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TravelAgentSQL.GetAllActiveTravelAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("todaydate", MySqlDbType.Datetime).Value = DateTime.Today;                
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    TravelAgent user = new TravelAgent(rd);
                    result.Add(user);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public DataSet ReportTravelAgentSummary(long taId)
        {
            DataSet result = new DataSet();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TravelAgentSQL.ReportTravelAgentSummary(), m_dataBaseConectivity.InterfaceSqlConnection);
                if (taId > 0)
                {
                    cmd.CommandText += " and ta.m_travelAgentId = ?m_travelAgentId ";
                    cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = taId;
                }
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(result);
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
            }
            return result;
        }

        public DataSet ReportTravelAgent(long id)
        {
            DataSet result = new DataSet();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TravelAgentSQL.ReportTravelAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                if (id > 0)
                {
                    cmd.CommandText += "where m_travelAgentId = ?m_travelAgentId ";
                    cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = id;
                }
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataAdapter rd = new MySqlDataAdapter(cmd);
                rd.Fill(result);
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public DataSet ReportByTravelAgent(long id)
        {
            DataSet result = new DataSet();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TravelAgentSQL.ReportByTravelAgent(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = id;
                MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                da.Fill(result);
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
    }
}
